<?php
require_once dirname(__FILE__) . '/config.php';
require_once 'Image/Graph.php';
require_once 'MDB2.php';

$arQueries = array(
    0 => array(
        'title' => 'Posts per month',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts

 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
    ),

    1 => array(
        'title' => 'Posts per month on pear-dev',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_group = 'php.pear.dev'
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
    ),

    2 => array(
        'title' => 'Call for votes per month',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_subject LIKE "[Pepr] Call for votes%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
    ),

    3 => array(
        'title' => 'Call for votes per month without Services_*',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_subject LIKE "[Pepr] Call for votes%"
    AND post_subject NOT LIKE "%Services_%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
    ),

    4 => array(
        'title' => 'Unique threads per month on pear-dev',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_group = 'php.pear.dev'
    AND post_subject NOT LIKE "%Re:%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
    ),

    5 => array(
        'title' => 'Posts by ###',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_from LIKE "%###%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
        ,
        'variable' => 'name'
    ),

    6 => array(
        'title' => 'CVS activity: ###',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_group = 'php.pear.cvs'
     AND post_subject LIKE "%###%"
     AND post_subject NOT LIKE "%Re:%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
        ,
        'variable' => 'name'
    ),

    7 => array(
        'title' => 'QA posts by ###',
        'query' => <<<EOQ
SELECT
 YEAR(post_date) as year, MONTH(post_date) as month,
 COUNT(*) as number
 FROM posts
 WHERE post_group = 'php.pear.qa'
     AND post_from LIKE "%###%"
 GROUP BY YEAR(post_date), MONTH(post_date)
EOQ
        ,
        'variable' => 'name'
    ),

);


$nQuery = 0;
if (isset($_GET['query']) && isset($arQueries[$_GET['query']])) {
    $nQuery = $_GET['query'];
}

$arQuery = $arQueries[$nQuery];
if (isset($arQuery['variable'])) {
    $strVar = $arQuery['variable'];
    if (!isset($_GET[$strVar])) {
        die('Please specify variable ' . $strVar);
    } else {
        $strValue = preg_replace('#[^a-zA-Z0-9_/-]#', '', $_GET[$strVar]);
        $arQuery['query'] = str_replace('###', $strValue, $arQuery['query']);
        $arQuery['title'] = str_replace('###', $strValue, $arQuery['title']);
    }
}

$Graph =& Image_Graph::factory('graph', array(700, 300));

$Graph->add(
    Image_Graph::vertical(
        Image_Graph::factory('title', array($arQuery['title'], 12)),
        $Plotarea = Image_Graph::factory('plotarea'),
        5
    )
);

$Arial =& $Graph->addNew('font', $GLOBALS['activitytracker']['graph']['font']);
$Arial->setSize($GLOBALS['activitytracker']['graph']['fontsize']);
$Arial->setColor('black');
$Graph->setFont($Arial);

$db = MDB2::connect($GLOBALS['activitytracker']['dsn']);
$db->loadModule('Extended', null, false);
$res = $db->query($arQuery['query']);
if (PEAR::isError($res)) {
    echo $res->getMessage() . "\n" . $res->getUserInfo() . "\n";
    die();
}
$dataset = Image_Graph::factory('dataset');

$arData = array();
for ($nYear = 2001; $nYear <= date('Y'); $nYear++) {
    for ($nMonth = 1; $nMonth <= 12; $nMonth++) {
        $obj = null;
        $obj->number = 0;
        $obj->year   = $nYear;
        $obj->month  = $nMonth;
        $arData[$nYear . '-' . $nMonth] = $obj;
    }
}
while ($row = $res->fetchRow(MDB2_FETCHMODE_OBJECT)) {
    $arData[$row->year . '-' . $row->month] = $row;
}

$n         = 0;
$nLastYear = 0;
$bOnce     = false;
$arLabels  = array();
foreach ($arData as $row) {
    $dataset->addPoint(++$n, $row->number);
    $arLabels[$n] = (($row->month == 6) ? $row->year : '');

    if ($row->year != $nLastYear) {
        if ($row->year % 2 == 0) {
            $MarkerX =& $Plotarea->addNew('Image_Graph_Axis_Marker_Area', null, IMAGE_GRAPH_AXIS_X);
            $MarkerX->setFillColor('yellow@0.3');
            $MarkerX->setLineColor('white');
            $MarkerX->setLowerBound($n);
            $MarkerX->setUpperBound(20000);
            $bOnce = true;
        } else {
            if ($bOnce) {
                $MarkerX->setUpperBound($n - 1);
            }
        }
        $nLastYear = $row->year;
    }
}

$AxisX = $Plotarea->getAxis(IMAGE_GRAPH_AXIS_X);
$ArrayData = Image_Graph::factory(
    'Image_Graph_DataPreprocessor_Array',
    array($arLabels)
);
$AxisX->setDataPreprocessor($ArrayData);

$Grid = $Plotarea->addNew('bar_grid', null, IMAGE_GRAPH_AXIS_Y);
$Grid->setFillColor('#339900@0.2');

$Plot = $Plotarea->addNew('step', array($dataset));


$Plot->setLineColor('#339900');
$Plot->setFillColor('#339900@0.8');

$Graph->done();
?>